package ems.database;

import ems.lib.*;
import ems.lib.Utility;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ResourceDAO {

    private Connection conn = null; // DBConnection.connect()
    private PreparedStatement ps = null;
    public Utility utility = new Utility();

    public ResourceDAO() {
        this.conn = (DBConnection.getConnection() != null) ? DBConnection.getConnection() : DBConnection.connect();
    }

    /**
     * Adds a resource to the resource table in the database.
     *
     * @param resource
     * :<BR> Resource object.
     * <br>
     * @return Returns the Id of the newly added resource. Returns 0 if there was a failure.
     */
    public int addResource(Resource resource) {
        int newID = 0;
        PreparedStatement psInsert = null;
        try {
            psInsert = conn.prepareStatement("INSERT INTO resource (resource, resource_no, status) values (?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            psInsert.setString(1, resource.getName());
            psInsert.setString(2, resource.getNumber());
            psInsert.setString(3, resource.getStatus());

            psInsert.executeUpdate();
            conn.commit();

            ResultSet resultSet = psInsert.getGeneratedKeys();

            if (resultSet != null && resultSet.next()) {
                newID = resultSet.getInt(1);
            }
            return newID;

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return 0;
    }//end addResource

    /**
     * Adds a resource to the resource table in the database.
     * @param name
     * :<BR>Name of the resource
     * @param number
     * :<BR>Resource number
     * @param status
     * :<BR>Status of the resource in string form; "Assigned" or "Unassigned"
     * @return Returns the ID of the added resource.
     */
    public int addResource(String name, String number, String status) {
        int newID = 0;
        PreparedStatement psInsert = null;
        try {
            psInsert = conn.prepareStatement("INSERT INTO resource "
                    + "(resource, resource_no, status) "
                    + "values (?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            psInsert.setString(1, name);
            psInsert.setString(2, number);
            psInsert.setString(3, status);

            psInsert.executeUpdate();
            conn.commit();

            ResultSet resultSet = psInsert.getGeneratedKeys();

            if (resultSet != null && resultSet.next()) {
                newID = resultSet.getInt(1);
            }
            return newID;

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return 0;
    }//end addResource

    /**
     * Adds a resource to the resource table in the database.
     * @param name
     * :<BR>Name of the resource
     * @param number
     * :<BR>Resource number
     * @param status
     * :<BR>Status of the resource in boolean form; True = "Assigned" or False = "Unassigned"
     * @return Returns the ID of the added resource.
     */
    public int addResource(String name, String number, boolean status) {
        int newID = 0;
        PreparedStatement psInsert = null;
        try {
            psInsert = conn.prepareStatement("INSERT INTO resource "
                    + "(resource, resource_no, status) "
                    + "values (?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            psInsert.setString(1, name);
            psInsert.setString(2, number);
            if (status) {
                psInsert.setString(3, "Assigned");
            } else {
                psInsert.setString(3, "Unassigned");
            }

            psInsert.executeUpdate();
            conn.commit();

            ResultSet resultSet = psInsert.getGeneratedKeys();

            if (resultSet != null && resultSet.next()) {
                newID = resultSet.getInt(1);
            }
            return newID;

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return 0;
    }//end addResource

    /**
     * Adds a resource to the resource table in the database. Status default to Unassigned
     * @param name
     * :<BR>Name of the resource
     * @param number
     * :<BR>Resource number
     * @return Returns the ID of the added resource.
     */
    public int addResource(String name, String number) {

        int newID = 0;
        PreparedStatement psInsert = null;
        try {
            psInsert = conn.prepareStatement("INSERT INTO resource "
                    + "(resource, resource_no, status) "
                    + "values (?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            psInsert.setString(1, name);
            psInsert.setString(2, number);
            psInsert.setString(3, "Unassigned");

            psInsert.executeUpdate();

            conn.commit();
            ResultSet resultSet = psInsert.getGeneratedKeys();

            if (resultSet != null && resultSet.next()) {
                newID = resultSet.getInt(1);
            }
            return newID;

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return 0;
    }//end addResource

    /**
     * Changes the status of a resource to 'Assigned'
     * @param id
     * :<BR>The ID of the resource
     */
    public void assign(int id) {
        PreparedStatement psUpdate = null;
        try {
            psUpdate = conn.prepareStatement("update resource set Status='Assigned'" + " where Resource_ID = " + id);
            psUpdate.executeUpdate();
            conn.commit();

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }//end assign

    /**
     * Changes the status of a resource to 'Unassigned'
     * @param id
     * :<BR>The ID of the resource
     */
    public void unAssign(int id) {
        PreparedStatement psUpdate = null;
        try {
            psUpdate = conn.prepareStatement("update resource set Status='Unassigned'" + " where Resource_ID = " + id);
            psUpdate.executeUpdate();
            conn.commit();

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }//end unAssign

    /**
     * Changes the status of a resource
     * @param id
     * :<BR>The ID of the resource
     * @param assignment
     * :<BR>The assignment that the status is to be changed to
     */
    public void assignment(int id, String assignment) {

        PreparedStatement psUpdate = null;
        try {
            psUpdate = conn.prepareStatement("update resource set Status = ? where Resource_ID = " + id);
            psUpdate.setString(1, assignment);
            psUpdate.executeUpdate();
            conn.commit();

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

    }

    /**
     * Changes the status of a resource
     * @param id
     * :<BR>The ID of the resource
     * @param assignment
     * :<BR>The assignment that the status is to be changed to. 1 = Assigned, 0 = Unassigned
     */
    public void assignment(int id, boolean assignment) {
        PreparedStatement psUpdate = null;
        try {
            psUpdate = conn.prepareStatement("update resource set Status = ? where Resource_ID = " + id);
            if (assignment) {
                psUpdate.setString(1, "Assigned");
            } else {
                psUpdate.setString(1, "Unassigned");
            }
            psUpdate.executeUpdate();
            conn.commit();

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

    }

    /**
     * Get resource from DB
     * @param id
     * :<BR> Id of desired Resource
     * @return Resource object. Will be null if resource could not be found.
     */
    public Resource getResource(int id) {

        Resource resource = null;
        Statement s = null;
        ResultSet rs = null;

        try {
            s = conn.createStatement();
            rs = s.executeQuery(
                    "SELECT Resource, Resource_no, status  FROM resource where Resource_ID=" + id);

            if (rs.next()) {
                String name = rs.getString("Resource");
                String number = rs.getString("Resource_no");
                String status = rs.getString("status");
                resource = new Resource(id, name, number, status);
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return resource;

    }

    /**
     * Get all resources with specific filter
     * @param filter Available | Unavailable
     * @return list of resources
     */
    public List<Resource> getAllResource(String filter) {
        List<Resource> lres = new ArrayList<Resource>();
        Resource res = null;
        try {
            String status = " ";
            if (filter.equalsIgnoreCase("available") || filter.equalsIgnoreCase("av")) {
                status = " WHERE status = 'AVAILABLE'";
            } else if (filter.equalsIgnoreCase("unavailable") || filter.equalsIgnoreCase("unav")) {
                status = " WHERE status = 'UNAVAILABLE'";
            }
            
            ps = conn.prepareStatement("SELECT * FROM Resource" + status + " ORDER BY status, resource");
            ResultSet resultSet = ps.executeQuery();
            int resid;
            while (resultSet.next()) {
                resid = resultSet.getInt("RESOURCE_ID");
                res = this.getResource(resid);
                lres.add(res);
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return lres;
    }

    /**
     * Get resources with certain type
     * @param filter
     * @param resType
     * @return list of resources
     */
    public List<Resource> getResourceWithType(String filter, String resType) {
        List<Resource> lres = new ArrayList<Resource>();
        Resource res = null;
        try {
            String status = " WHERE 1=1 ";
            if (filter.equalsIgnoreCase("available") || filter.equalsIgnoreCase("av")) {
                status += " AND status = 'AVAILABLE' ";
            } else if (filter.equalsIgnoreCase("unavailable") || filter.equalsIgnoreCase("unav")) {
                status += " AND status = 'UNAVAILABLE' ";
            }

            if (!resType.isEmpty()) { status += " AND resource = '" + resType + "' ";}

            ps = conn.prepareStatement("SELECT * FROM Resource" + status);
            ResultSet resultSet = ps.executeQuery();
            int resid;
            while (resultSet.next()) {
                resid = resultSet.getInt("RESOURCE_ID");
                res = this.getResource(resid);
                lres.add(res);
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return lres;
    }

    /**
     * Search for resource by name
     * @param name
     * :<BR>Resource name.
     * @return Returns an Object containing all the resoruces with matching name.
     */
    public Object[][] findResource(String name) {
        Object[][] resources = {};
        Statement s = null;
        ResultSet rs = null;

        try {
            s = conn.createStatement();
            rs = s.executeQuery("SELECT Resource_ID, Resource, Resource_No, Status FROM resource where Resource = '" + name + "'");
            resources = utility.resultSetToObjectArray(rs);



        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return resources;

    }

    /**
     * Returns all Unassigned Resources.
     * @return Object[][] type
     */
    public Object[][] findUnassigned() {
        Object[][] resources = {};
        Statement s = null;
        ResultSet rs = null;

        try {
            s = conn.createStatement();
            rs = s.executeQuery("SELECT Resource_ID, Resource, Resource_No, Status FROM resource where Status = 'Unassigned'");
            resources = utility.resultSetToObjectArray(rs);

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return resources;
    }

    /**
     * Returns unassigned resources of a given name
     * @param name
     * :<BR> Name of desired resource
     * @return Object[][] type
     */
    public Object[][] findUnassigned(String name) {
        Object[][] resources = {};
        Statement s = null;
        ResultSet rs = null;

        try {
            s = conn.createStatement();
            rs = s.executeQuery("SELECT Resource_ID, Resource, Resource_No, Status FROM resource where Status = 'Unassigned' and Resource = '" + name + "'");
            resources = utility.resultSetToObjectArray(rs);

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return resources;
    }
}
